USE [TBbilling]
GO
/****** Object:  StoredProcedure [dbo].[BatchPay]    Script Date: 02/06/2015 15:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- drop procedure BatchPay
-- exec BatchPay '5/12/2014','1/15/2015'
--===============================================
CREATE PROCEDURE [dbo].[BatchPay]  @FromDT datetime, @ToDT DateTime
	
AS
BEGIN
Set nocount on

Declare 
	@option  char(3)
	
	



set	@option  = 'ALL'



select distinct
  isnull(pc.Patient_Class,'') 'Patient Class'
  ,'' 'Bad Debt Code'
  ,'' 'Bad Debt Date'
  ,'' 'Bad Debt Amount'
  ,'' 'Bad Debt Recovery Amount'
   ,isnull(e.Patient_Name,'') 'Patient Name'
    , isnull(p.DOB,'') 'Patient Date of Birth'  
    ,b.Bill_No    
  -- , isnull(b.Bill_No,'') 'Patient Account Number'
   , case p.SSN
		 when '   -  -    ' then '111-11-111'
		 else isnull(p.SSN,'')
		 end ' Patient SSN Number'
	 
  , isnull(smc.Relationship,'') AS 'NOK Relationship'
    , isnull(smc.Person_Name,'') AS 'NOK Name'
    , ''as 'NOK Phone Number' --ISNULL (smc.Home_phone,'') as  'NOK Phone Number'
    , isnull((b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3),0) Balance
    , isnull(b.total_charges1+b.total_charges2+b.total_charges3,0) 'Total Charges'
    , (Select isnull(sum(isnull(amount,0)),0) from Angus.dbo.shr_transactions where billid=b.billid and Action_Type='A') 'Total Adjustment'
    , isnull((Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3),0) - (Select isnull(sum(isnull(amount,0)),0) from Angus.dbo.shr_transactions where billid=b.billid and Action_Type='A') 'Total Payments'
 
    , isnull(g.Person_Name,'') 'Guarantor Name'
   , isnull(gp.Address1,'') 'Guarentor Address 1'
   , isnull(gp.Address2,'') 'Guarentor Address 2'
   , isnull(gp.City,'') 'Guarentor City'
   , isnull(gp.State_Code,'') 'Guarentor State'
   , isnull(gp.Zip,'') 'Guarentor Zip Code'
   , isnull(gp.Home_Phone,'') 'Guarentor Phone'
     , isnull(convert(char(10),gp.DOB,101),'') 'Guarentor Date of Birth'
    , isnull(gp.SSN,'') 'Guarentor SSN'
       , isnull(gemployer.Name,'') 'Guarentor EmployerName'
       ,'' 'Guarentor Emp Address 1'
,'' 'Guarentor Emp Address 2'
    ,'' 'Guarentor Emp City'
    ,'' 'Guarentor Emp State'
    ,'' 'Guarentor Emp Zip Code'
    , isnull(gp.Work_phone,'') 'Guarentor Employer Phone' --, gemployer.Phone
   ,fc.Financial_Class 'Financial_Class'

   , p.Address1 AS Patient_Address1
   , p.Address2 AS Patient_Address2
   , p.City AS Patient_City
   , p.Zip AS Patient_Zip
   , p.State_Code AS Patient_State_Code
   , p.Home_Phone
   , p.Work_phone
   , p.Other_Phone
   , p.Other_Phone2
   , p.Email
   , e.Admission_Datetime
   , e.Discharge_datetime
   , p.Sex
   , gp.Other_Phone 'Guarantor Other Phone'
   , afc.Financial_Class 'Admit FC'
   , ic.Name Insurance
   , gpi.Insurance_No Subscriber#


  from Angus.dbo.ar_bills b (nolock)
   inner join Angus.dbo.shr_Financial_classes fc (nolock) on b.current_financial_classid=fc.financial_classid and fc.companyID=2
   inner join Angus.dbo.b_charges bc (nolock) on b.bill_no=bc.bill_no and b.episodeid=bc.episodeid
   inner join Angus.dbo.shr_charge_codes c (nolock) on c.Charge_CodeID=bc.Charge_CodeID
   inner join Angus.dbo.shr_UB92_Revenue_Codes rc on c.Revenue_CodeId=rc.Revenue_Code_Id
   inner join Angus.dbo.shr_episodes e (nolock) on b.episodeid=e.episodeid
   inner join Angus.dbo.glb_People_insurances gpi (nolock) on e.Patient_InsuranceID1=gpi.People_InsuranceID
   inner Join Angus.dbo.glb_Insurance_Companies ic With (NoLock) on gpi.Insurance_CompanyID=ic.Insurance_CompanyID
   inner Join Angus.dbo.shr_Insurance_Company_Fin_Classes icf (nolock) on gpi.Insurance_CompanyID=icf.Insurance_CompanyID
   inner join Angus.dbo.shr_Financial_classes afc (nolock) on icf.financial_classid=afc.financial_classid and afc.companyID=2
   inner join Angus.dbo.shr_mpi mpi (nolock) on mpi.mpiid=e.mpiid

  	outer apply TBbilling.dbo.getnextofKin (mpi.MPIID) smc
  
   inner join Angus.dbo.glb_people p (nolock) on p.peopleid=mpi.peopleid
   left join Angus.dbo.glb_Employers gemployer1 (nolock) on gemployer1.EmployerID=p.EmployerID
   inner join Angus.dbo.shr_patient_classes pc (nolock) on e.patient_classid=pc.patient_classid and pc.companyid=2
   inner join Angus.dbo.shr_Guarantors g (nolock) on g.guarantorid=b.guarantorid
   inner join Angus.dbo.glb_people gp (nolock) on gp.peopleid=g.peopleid
   left join Angus.dbo.glb_Employers gemployer (nolock) on gemployer.EmployerID=gp.EmployerID
   left join Angus.dbo.shr_transactions trs (nolock) on ( b.billid= trs.billid)
   inner join TBbilling.dbo.NewDemographics TBInfo on (b.BillID = TBInfo.billid
														and b.EpisodeID = TBInfo.episodeID)
 WHERE
   fc.Financial_Class in ('TB','TA','TR','TI','TM')
   and (1=CASE WHEN @option='Z' AND (b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)=0  THEN 1
   WHEN @option<>'Z' AND (b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)>0  THEN 1
   ELSE 0
   END)
   and CHARINDEX(ISNULL(b.STATUS,''),CASE WHEN (@option='ALL' OR @option='Z') --All Bills    
            THEN ''
            ELSE 'B'    --Bad Debt Bills    
          END)
         =CASE WHEN @option='B'
            THEN 1
            ELSE 0
         END
  group by b.Bill_No
-- ,b.status   

   , e.Patient_Name
   , p.Address1
   , p.Address2
   , p.City
   , p.Zip
   , p.State_Code
   , e.Admission_Datetime
   , e.discharge_datetime
   , p.SSN
   , p.DOB
   , p.Sex
   , g.Guarantor_No
   , g.Person_Name
   , gp.SSN
   , gp.Address1
   , gp.Address2
   , gp.City
   , gp.State_Code
   , gp.Zip
   , gp.Home_Phone
   , gp.Work_phone
   , gp.Other_Phone
   , afc.Financial_Class
   , ic.Name
   , gpi.Insurance_No
   , gemployer.Name
   , gemployer.Phone
   , b.total_charges1+b.total_charges2+b.total_charges3     
   , (Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)    
   , b.billid
   , (b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)     
   , fc.Financial_Class
   , pc.Patient_Class
   , rc.code
   , gp.DOB
   , smc.Person_Name
   , smc.Relationship
   , smc.home_phone
   , gemployer1.Name
   , gemployer.Name
    , p.Home_Phone
   , p.Work_phone
   , p.Other_Phone
   , p.Other_Phone2
   , p.Email

order by b.Bill_No
   --, Admission_Datetime
   

   
set nocount off

End
GO
